package humanMirror.model.questionLibrary;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.util.ArrayList;

public class SqlWorker implements SqlWorkerInterfase{
	private Connection connection;
	private PreparedStatement ps;
	private ResultSet rs;
	private int answerNumber;
	private String path="jdbc:sqlite:c:\\HM.sqlite";
	
	public SqlWorker()  {
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager
					.getConnection(path);
			answerNumber = getCountOf("answers");
		} catch (ClassNotFoundException e) {
			System.out.print("Can't connect to database");
			System.exit(0);
		}
		catch (SQLException e) {
			System.out.print("Can't connect to database " + e.toString());
			System.exit(0);
		}

	}

	public SqlWorker(String base, String user, String password)
			throws SQLException {
		connection = DriverManager.getConnection(base, user, password);
		answerNumber = getCountOf("answers");
	}

	public String[] getAnswers(int numberOfQuestion)// !+1
			throws SQLDataException, SQLException {
		return getStringArrayResult(numberOfQuestion +1,
				"SELECT answer FROM answers WHERE Question=?", "Answer");
	}

	public String getQuestion(int numberOfQuestion) throws SQLDataException,
			SQLException {
		return getStringResult(numberOfQuestion + 1,
				"SELECT Question FROM questions WHERE Id=?");
	}

	public int getCountOfQuestions() throws SQLException {
		return getCountOf("questions");
	}

	public int currentEffect(int organ, int param, int questionNumber,
			int numberOfAnswer) throws SQLException {
		return getIntResult(queryMaker(param, organ, questionNumber,
				numberOfAnswer));
	}

	// ---------------------------not used yet----------------------------
	public void addQuestion(String question) throws SQLException {
		ps = connection
				.prepareStatement("insert into questions values(NULL,?)");
		ps.setString(1, question);
		ps.executeUpdate();
	}

	public void removeQuestion(int id) throws SQLException {
		ps = connection.prepareStatement("delete from questions where id="
				+ String.valueOf(id));
		ps.executeUpdate();
	}

	public void addAnswer(int qNum, String answer, int[] results)
			throws SQLException {
		ps = connection.prepareStatement("insert into answers values "
				+ valuesBuilder(qNum, answer, results));
		ps.executeUpdate();
	}

	public void removeAnswer(int questionNum, int answerNum)
			throws SQLException {
		ps = connection.prepareStatement("delete from answers where question="
				+ String.valueOf(questionNum) + " and answerNum="
				+ String.valueOf(answerNum));
		ps.executeUpdate();
	}

	public void setOrganParams(String name, int... params) throws SQLException {
		int numerOfAnwers = (params.length > answerNumber) ? answerNumber
				: params.length; // ?
		for (int id = 1; id <= numerOfAnwers; id++) {
			ps = connection.prepareStatement("update answers set " + name + "="
					+ String.valueOf(params[id - 1]) + " where id="
					+ String.valueOf(id));
			ps.executeUpdate();
		}
	}

	public void removeOrganParams(String... names) throws SQLException {
		for (String column : names) {
			ps = connection.prepareStatement("alter table answers drop COLUMN "
					+ column);
			ps.executeUpdate();
		}
	}

	public void addOrganParams(String... names) throws SQLException {
		for (String column : names) {
			ps = connection.prepareStatement("alter table answers add COLUMN "
					+ column + " int" + " default 0");
			ps.executeUpdate();
		}
	}

	int getIntResult(int param, String query) throws SQLException {
		execute(param, query);
		return getIntResult();
	}

	int getIntResult(String query) throws SQLException {
		execute(query);
		int result = 0;
		if (rs.next()) {
			result = rs.getInt(1);
		} else
			throw new SQLDataException(
					"This data does not exist, or is not an int");
		// rs.close();
		return result;
	}

	String getStringResult(int param, String query) throws SQLDataException,
			SQLException {
		execute(param, query);
		return getStringResult();
	}

	String[] getStringArrayResult(int param, String query, String column)
			throws SQLDataException, SQLException {
		execute(param, query);
		return getStringArrayResult(column);
	}

	private void execute(String query) throws SQLException {
		ps = connection.prepareStatement(query);
		rs = ps.executeQuery();

		// ps.close();
	}

	private void execute(int param, String query) throws SQLException {
		ps = connection.prepareStatement(query);
		ps.setInt(1, param);
		rs = ps.executeQuery();

		// ps.close();
	}

	private String getStringResult() throws SQLDataException, SQLException {
		String result = "";
		if (rs.next()) {
			result = rs.getString(1);
		} else
			throw new SQLDataException(
					"This data does not exist, or is not a string");
		// rs.close();
		return result;
	}

	String[] getStringArrayResult(String column) throws SQLDataException,
			SQLException {
		ArrayList<String> answers = new ArrayList<String>();
		while (rs.next()) {
			answers.add(rs.getString(column));
		}
		String[] result = new String[answers.size()];
		answers.toArray(result);
		// rs.close();
		return result;
	}

	private int getIntResult() throws SQLDataException, SQLException {
		int result = 0;
		if (rs.next()) {
			result = rs.getInt(1);
		} else
			throw new SQLDataException(
					"This data does not exist, or is not an int");
		// rs.close();
		return result;
	}

	private int getCountOf(String tableName) throws SQLException {
		execute("SELECT * FROM " + tableName);
		int result = 0;
		while (rs.next()) {
			result = rs.getInt("Id");
		}
		return result;
	}

	public void disconnect() throws SQLException {
		ps.close();
		rs.close();

		connection.close();

	}

	private String valuesBuilder(int qNum, String answer, int[] results)
			throws SQLException {
		StringBuilder sb = new StringBuilder("(NULL,");
		sb.append(String.valueOf(qNum) + ",");
		sb.append(String.valueOf(getNextanswerNum(qNum)) + ",");
		sb.append("'" + answer + "'");
		for (int point : results) {
			sb.append("," + String.valueOf(point));
		}
		sb.append(")");
		return sb.toString();
	}

	private int getNextanswerNum(int qNum) throws SQLException {
		int aNum = 0;
		execute(qNum, "select AnswerNum from answers where question=?");
		while (rs.next()) {
			aNum = rs.getInt("AnswerNum");
		}
		aNum++;
		return aNum;
	}

	private String chooseOrgan(int organ) {
		String organName = "Brain_";
		switch (organ) {
		case 0:
			organName = "Brain_";
			break;
		case 1:
			organName = "Liver_";
			break;
		case 2:
			organName = "Stomack_";
			break;
		case 3:
			organName = "Lungs_";
			break;
		case 4:
			organName = "Heart_";
			break;
		}
		return organName;
	}

	private String queryMaker(int param, int organ, int questionNumber,
			int answer) {
		StringBuilder query = new StringBuilder();
		query.append("select ");
		query.append(chooseOrgan(organ));
		query.append(param);
		query.append(" from answers where question=");
		query.append(questionNumber + 1);
		query.append(" and answernum=");
		query.append(answer + 1);
		return query.toString();

	}
}
